<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
	<title>Full-Text Search Functions | ElasticSearch 7.7 权威指南中文版</title>
	<meta name="keywords" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <meta name="description" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <!-- Give IE8 a fighting chance -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
	<link rel="stylesheet" type="text/css" href="../static/styles.css" />
	<script>
	var _link = 'sql-functions-search.html';
    </script>
</head>
<body>
<div class="main-container">
    <section id="content">
        <div class="content-wrapper">
            <section id="guide" lang="zh_cn">
                <div class="container">
                    <div class="row">
                        <div class="col-xs-12 col-sm-8 col-md-8 guide-section">
                            <div style="color:gray; word-break: break-all; font-size:12px;">原英文版地址: <a href="https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-functions-search.html" rel="nofollow" target="_blank">https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-functions-search.html</a>, 原文档版权归 www.elastic.co 所有<br/>本地英文版地址: <a href="../en/sql-functions-search.html" rel="nofollow" target="_blank">../en/sql-functions-search.html</a></div>
                        <!-- start body -->
                  <div class="page_header">
<strong>重要</strong>: 此版本不会发布额外的bug修复或文档更新。最新信息请参考 <a href="https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html" rel="nofollow">当前版本文档</a>。
</div>
<div id="content">
<div class="breadcrumbs">
<span class="breadcrumb-link"><a href="index.html">Elasticsearch Guide [7.7]</a></span>
»
<span class="breadcrumb-link"><a href="xpack-sql.html">SQL access</a></span>
»
<span class="breadcrumb-link"><a href="sql-functions.html">Functions and Operators</a></span>
»
<span class="breadcrumb-node">Full-Text Search Functions</span>
</div>
<div class="navheader">
<span class="prev">
<a href="sql-functions-datetime.html">« Date/Time and Interval Functions and Operators</a>
</span>
<span class="next">
<a href="sql-functions-math.html">Mathematical Functions »</a>
</span>
</div>
<div class="section xpack">
<div class="titlepage"><div><div>
<h2 class="title">
<a id="sql-functions-search"></a>Full-Text Search Functions<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/search.asciidoc">edit</a><a class="xpack_tag" href="https://www.elastic.co/subscriptions"></a>
</h2>
</div></div></div>
<p>Search functions should be used when performing full-text search, namely
when the <code class="literal">MATCH</code> or <code class="literal">QUERY</code> predicates are being used.
Outside a, so-called, search context, these functions will return default values
such as <code class="literal">0</code> or <code class="literal">NULL</code>.</p>
<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-search-match"></a><code class="literal">MATCH</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/search.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">MATCH(
    field_exp,   <a id="CO114-1"></a><i class="conum" data-value="1"></i>
    constant_exp <a id="CO114-2"></a><i class="conum" data-value="2"></i>
    [, options]) <a id="CO114-3"></a><i class="conum" data-value="3"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO114-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>field(s) to match</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO114-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>matching text</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO114-3"><i class="conum" data-value="3"></i></a></p>
</td>
<td align="left" valign="top">
<p>additional parameters; optional</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Description</strong></span>: A full-text search option, in the form of a predicate, available in Elasticsearch SQL that gives the user control over powerful <a class="xref" href="query-dsl-match-query.html" title="Match query">match</a>
and <a class="xref" href="query-dsl-multi-match-query.html" title="Multi-match query">multi_match</a> Elasticsearch queries.</p>
<p>The first parameter is the field or fields to match against. In case it receives one value only, Elasticsearch SQL will use a <code class="literal">match</code> query to perform the search:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT author, name FROM library WHERE MATCH(author, 'frank');

    author     |       name
---------------+-------------------
Frank Herbert  |Dune
Frank Herbert  |Dune Messiah
Frank Herbert  |Children of Dune
Frank Herbert  |God Emperor of Dune</pre>
</div>
<p>However, it can also receive a list of fields and their corresponding optional <code class="literal">boost</code> value. In this case, Elasticsearch SQL will use a
<code class="literal">multi_match</code> query to match the documents:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT author, name, SCORE() FROM library WHERE MATCH('author^2,name^5', 'frank dune');

    author     |       name        |    SCORE()
---------------+-------------------+---------------
Frank Herbert  |Dune               |11.443176
Frank Herbert  |Dune Messiah       |9.446629
Frank Herbert  |Children of Dune   |8.043278
Frank Herbert  |God Emperor of Dune|7.0029488</pre>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>The <code class="literal">multi_match</code> query in Elasticsearch has the option of <a class="xref" href="query-dsl-multi-match-query.html" title="Multi-match query">per-field boosting</a> that gives preferential weight
(in terms of scoring) to fields being searched in, using the <code class="literal">^</code> character. In the example above, the <code class="literal">name</code> field has a greater weight in
the final score than the <code class="literal">author</code> field when searching for <code class="literal">frank dune</code> text in both of them.</p>
</div>
</div>
<p>Both options above can be used in combination with the optional third parameter of the <code class="literal">MATCH()</code> predicate, where one can specify
additional configuration parameters (separated by semicolon <code class="literal">;</code>) for either <code class="literal">match</code> or <code class="literal">multi_match</code> queries. For example:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT author, name, SCORE() FROM library WHERE MATCH(name, 'to the star', 'operator=or;cutoff_frequency=0.2');

     author      |                name                |    SCORE()
-----------------+------------------------------------+---------------
Peter F. Hamilton|Pandora's Star                      |3.0997515
Douglas Adams    |The Hitchhiker's Guide to the Galaxy|3.1756816</pre>
</div>
<p>In the more advanced example above, the <code class="literal">cutoff_frequency</code> parameter allows specifying an absolute or relative document frequency where
high frequency terms are moved into an optional subquery and are only scored if one of the low frequency (below the cutoff) terms in the
case of an <code class="literal">or</code> operator or all of the low frequency terms in the case of an <code class="literal">and</code> operator match. More about this you can find in the
<a class="xref" href="query-dsl-match-query.html#query-dsl-match-query-cutoff" title="Cutoff frequency">Cutoff frequency</a> page.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>The allowed optional parameters for a single-field <code class="literal">MATCH()</code> variant (for the <code class="literal">match</code> Elasticsearch query) are: <code class="literal">analyzer</code>, <code class="literal">auto_generate_synonyms_phrase_query</code>,
<code class="literal">cutoff_frequency</code>, <code class="literal">lenient</code>, <code class="literal">fuzziness</code>, <code class="literal">fuzzy_transpositions</code>, <code class="literal">fuzzy_rewrite</code>, <code class="literal">minimum_should_match</code>, <code class="literal">operator</code>,
<code class="literal">max_expansions</code>, <code class="literal">prefix_length</code>.</p>
</div>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>The allowed optional parameters for a multi-field <code class="literal">MATCH()</code> variant (for the <code class="literal">multi_match</code> Elasticsearch query) are: <code class="literal">analyzer</code>, <code class="literal">auto_generate_synonyms_phrase_query</code>,
<code class="literal">cutoff_frequency</code>, <code class="literal">lenient</code>, <code class="literal">fuzziness</code>, <code class="literal">fuzzy_transpositions</code>, <code class="literal">fuzzy_rewrite</code>, <code class="literal">minimum_should_match</code>, <code class="literal">operator</code>,
<code class="literal">max_expansions</code>, <code class="literal">prefix_length</code>, <code class="literal">slop</code>, <code class="literal">tie_breaker</code>, <code class="literal">type</code>.</p>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-search-query"></a><code class="literal">QUERY</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/search.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">QUERY(
    constant_exp <a id="CO115-1"></a><i class="conum" data-value="1"></i>
    [, options]) <a id="CO115-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO115-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>query text</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO115-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>additional parameters; optional</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Description</strong></span>: Just like <code class="literal">MATCH</code>, <code class="literal">QUERY</code> is a full-text search predicate that gives the user control over the <a class="xref" href="query-dsl-query-string-query.html" title="Query string query">query_string</a> query in Elasticsearch.</p>
<p>The first parameter is basically the input that will be passed as is to the <code class="literal">query_string</code> query, which means that anything that <code class="literal">query_string</code>
accepts in its <code class="literal">query</code> field can be used here as well:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT author, name, SCORE() FROM library WHERE QUERY('name:dune');

    author     |       name        |    SCORE()
---------------+-------------------+---------------
Frank Herbert  |Dune               |2.2886353
Frank Herbert  |Dune Messiah       |1.8893257
Frank Herbert  |Children of Dune   |1.6086556
Frank Herbert  |God Emperor of Dune|1.4005898</pre>
</div>
<p>A more advanced example, showing more of the features that <code class="literal">query_string</code> supports, of course possible with Elasticsearch SQL:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT author, name, page_count, SCORE() FROM library WHERE QUERY('_exists_:"author" AND page_count:&gt;200 AND (name:/star.*/ OR name:duna~)');

      author      |       name        |  page_count   |    SCORE()
------------------+-------------------+---------------+---------------
Frank Herbert     |Dune               |604            |3.7164764
Frank Herbert     |Dune Messiah       |331            |3.4169943
Frank Herbert     |Children of Dune   |408            |3.2064917
Frank Herbert     |God Emperor of Dune|454            |3.0504425
Peter F. Hamilton |Pandora's Star     |768            |3.0
Robert A. Heinlein|Starship Troopers  |335            |3.0</pre>
</div>
<p>The query above uses the <code class="literal">_exists_</code> query to select documents that have values in the <code class="literal">author</code> field, a range query for <code class="literal">page_count</code> and
regex and fuzziness queries for the <code class="literal">name</code> field.</p>
<p>If one needs to customize various configuration options that <code class="literal">query_string</code> exposes, this can be done using the second <em>optional</em> parameter.
Multiple settings can be specified separated by a semicolon <code class="literal">;</code>:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT author, name, SCORE() FROM library WHERE QUERY('dune god', 'default_operator=and;default_field=name');

    author     |       name        |    SCORE()
---------------+-------------------+---------------
Frank Herbert  |God Emperor of Dune|3.6984892</pre>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>The allowed optional parameters for <code class="literal">QUERY()</code> are: <code class="literal">allow_leading_wildcard</code>, <code class="literal">analyze_wildcard</code>, <code class="literal">analyzer</code>,
<code class="literal">auto_generate_synonyms_phrase_query</code>, <code class="literal">default_field</code>, <code class="literal">default_operator</code>, <code class="literal">enable_position_increments</code>,
<code class="literal">escape</code>, <code class="literal">fuzziness</code>, <code class="literal">fuzzy_max_expansions</code>, <code class="literal">fuzzy_prefix_length</code>, <code class="literal">fuzzy_rewrite</code>, <code class="literal">fuzzy_transpositions</code>,
<code class="literal">lenient</code>, <code class="literal">max_determinized_states</code>, <code class="literal">minimum_should_match</code>, <code class="literal">phrase_slop</code>, <code class="literal">rewrite</code>, <code class="literal">quote_analyzer</code>,
<code class="literal">quote_field_suffix</code>, <code class="literal">tie_breaker</code>, <code class="literal">time_zone</code>, <code class="literal">type</code>.</p>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-search-score"></a><code class="literal">SCORE</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/search.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SCORE()</pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>: <em>none</em></p>
<p><span class="strong strong"><strong>Output</strong></span>: <code class="literal">double</code> numeric value</p>
<p><span class="strong strong"><strong>Description</strong></span>: Returns the <a href="https://www.elastic.co/guide/en/elasticsearch/guide/2.x/relevance-intro.html" class="ulink" target="_top">relevance</a> of a given input to the executed query.
The higher score, the more relevant the data.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>When doing multiple text queries in the <code class="literal">WHERE</code> clause then, their scores will be
combined using the same rules as Elasticsearch’s
<a class="xref" href="query-dsl-bool-query.html" title="Boolean query">bool query</a>.</p>
</div>
</div>
<p>Typically <code class="literal">SCORE</code> is used for ordering the results of a query based on their relevance:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY SCORE() DESC;

    SCORE()    |    author     |       name        |  page_count   |    release_date
---------------+---------------+-------------------+---------------+--------------------
2.2886353      |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00Z
1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00Z
1.6086556      |Frank Herbert  |Children of Dune   |408            |1976-04-21T00:00:00Z
1.4005898      |Frank Herbert  |God Emperor of Dune|454            |1981-05-28T00:00:00Z</pre>
</div>
<p>However, it is perfectly fine to return the score without sorting by it:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT SCORE() AS score, name, release_date FROM library WHERE QUERY('dune') ORDER BY YEAR(release_date) DESC;

     score     |       name        |    release_date
---------------+-------------------+--------------------
1.4005898      |God Emperor of Dune|1981-05-28T00:00:00Z
1.6086556      |Children of Dune   |1976-04-21T00:00:00Z
1.8893257      |Dune Messiah       |1969-10-15T00:00:00Z
2.2886353      |Dune               |1965-06-01T00:00:00Z</pre>
</div>
</div>

</div>
<div class="navfooter">
<span class="prev">
<a href="sql-functions-datetime.html">« Date/Time and Interval Functions and Operators</a>
</span>
<span class="next">
<a href="sql-functions-math.html">Mathematical Functions »</a>
</span>
</div>
</div>

                  <!-- end body -->
                        </div>
                        <div class="col-xs-12 col-sm-4 col-md-4" id="right_col">
                        
                        </div>
                    </div>
                </div>
            </section>
        </div>
    </section>
</div>
<script src="../static/cn.js"></script>
</body>
</html>